﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;

namespace SYcwpb.Helpers
{
    public class ExcelHelper
    {
        private Application excelApp;
        private Workbook wb;
        private string fileName = string.Empty;

        public ExcelHelper(string filename)
        {
            excelApp = new Application();
            object objOpt = System.Reflection.Missing.Value;
            wb = (Workbook)excelApp.Workbooks.Open(filename, objOpt, false,
                objOpt, objOpt, objOpt, true, objOpt, 
                objOpt, true, objOpt, objOpt, objOpt, 
                objOpt, objOpt);
        }

        /// <summary>
        /// 所有sheet的名称列表
        /// </summary>
        /// <returns></returns>
        public List<string> GetSheetNames()
        {
            List<string> list = new List<string>();
            Sheets sheets = wb.Worksheets;
            string sheetNams = string.Empty;
            foreach (Worksheet sheet in sheets)
            {
                list.Add(sheet.Name);
            }
            return list;
        }

        public System.Data.DataTable GetSimpleDataTable(string sheetName)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            object[,] data = GetContent(sheetName);
            Worksheet sheet = GetWorksheetByName(sheetName);
            int colCounts = sheet.UsedRange.Columns.Count;
            int rowCounts = sheet.UsedRange.Rows.Count;
            for (int i = 0; i < colCounts; i++)
            {
                DataColumn col = new DataColumn();
                dt.Columns.Add(col);
            }
            for (int i = 2; i <= rowCounts; i++)
            {
                System.Data.DataRow row = dt.NewRow();
                
                for (int p = 1; p <= colCounts; p++)
                {
                    row[p-1] = data[i, p];
                }
                dt.Rows.Add(row);
            }

            return dt;
        }
        public Worksheet GetWorksheetByName(string name)
        {
            Worksheet sheet = null;
            Sheets sheets = wb.Worksheets;
            foreach (Worksheet s in sheets)
            {
                if (s.Name == name)
                {
                    sheet = s;
                    break;
                }
            }
            return sheet;
        }

        /// 
        /// </summary>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public object[,] GetContent(string sheetName)
        {
            Worksheet sheet = GetWorksheetByName(sheetName);
            //获取A1 到AM24范围的单元格
            //Range rang = sheet.get_Range("A1", "AM24");
            //读一个单元格内容
            //sheet.get_Range("A1", Type.Missing);
            //不为空的区域,列,行数目
            //   int l = sheet.UsedRange.Columns.Count;
            // int w = sheet.UsedRange.Rows.Count;
            object[,] dell = sheet.UsedRange.get_Value(Missing.Value) as object[,];
            //System.Array values = (Array)rang.Cells.Value2;
            return dell;
        }
        /// </summary>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public Array GetContent(string sheetName,string cellName)
        {
            Worksheet sheet = GetWorksheetByName(sheetName);
            //int l = sheet.UsedRange.Columns.Count;
            int w = sheet.UsedRange.Rows.Count;
            Range rang = sheet.get_Range(cellName+1, cellName+w);
            //  object[,] dell = sheet.UsedRange.get_Value(Missing.Value) as object[,];
            System.Array values = (Array)rang.Cells.Value2;
            return values;
        }
        public void Close()
        {
            wb.Close();
            wb = null;
            excelApp.Quit();
            excelApp = null;
        }
    }
}
